import pandas as pd
import tushare as ts
import math
from sqlalchemy import create_engine
def getTag(x):
    tag=math.floor(x)
    if tag>=8 :
        return 8
    elif tag<=-8:
        return -8
    else:
        return tag
dbpath='/home/xzh/data/stockdata2018.db'
engine= create_engine('sqlite:////'+dbpath)
code='sh'
#stdata = pd.read_sql("select * from stocks where date>'2016-10-01' and code not in('sz','sh') ".format(code),con= engine)
#stdata=stdata.sort_index()
#stdata['year']=stdata['date'].apply(lambda x:x[0:4])
#stdata['date']=stdata['date'].apply(lambda x:int(str(x).replace("-","")))
o= pd.read_sql("select code,close from stocks where date='2018-10-10' and code not in('sz','sh') ".format(code),con= engine) #stdata[stdata['date']==20161010][['code','close']]
n= pd.read_sql("select code,close from stocks where date='2019-11-15' and code not in('sz','sh') ".format(code),con= engine) #stdata[stdata['date']==20191113][['code','close']]
o=o.merge(n,on='code',how='inner')
o['diffs']=o.close_y-o.close_x
o['p_change']=o['diffs']*100/o.close_x
o['tag']=o['p_change'].apply(getTag)
o.sort_values('p_change',inplace=True)
o.reset_index(inplace=True,drop=True)

st=o.groupby('tag')['code'].count()
guoqi=eval(open('../国企.txt','r') .read())#pickle.load(open('stock/国企.plk','rb'))
o['国企']=o['code'].apply(lambda x: 'Y' if x in guoqi.keys() else '')
o1=o.merge(stocklist,left_on='code',right_index=True)
gu=o1[o1['国企']=='Y']